CREATE POLICY
CREATE POLICY — Define a new row-level security policy for a table
Synopsis
CREATE POLICY name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]
Description
CREATE POLICY defines a new row-level security policy for a table. Note that in order for a created policy to be applied, row-level security must be enabled on the table (using ALTER TABLE ... ENABLE ROW LEVEL SECURITY).
A policy grants the permission to select, insert, update, or delete rows that match the associated policy expression. Existing table rows are checked against the expression specified in USING, while new rows to be created through INSERT or UPDATE are checked against the expression specified in WITH CHECK. When the USING expression returns true for a given row, that row is visible to the user; when it returns false or null, the row is invisible. When a WITH CHECK expression returns true for a row, the row is allowed to be inserted or updated; if it returns false or null, an error is raised.
For INSERT and UPDATE statements, the WITH CHECK expression is enforced after BEFORE triggers are fired and before any data modification actually takes place. Therefore, a BEFORE ROW trigger can modify the data being inserted, thereby affecting the result of the security policy check. The WITH CHECK expression is enforced before any other constraints.
Policy names are per-table. Therefore, a policy name can be used for many different tables, with an appropriate definition for each table.
Policies can be applied to specific commands or specific roles. Unless otherwise specified, the default behavior for a newly created policy is to apply to all commands and roles. Multiple policies can apply to a single command; see below for details.
For policies that have both USING and WITH CHECK expressions (ALL and UPDATE), if no WITH CHECK expression is defined, then the USING expression will be used to determine which rows are visible (the normal USING case) and which new rows are allowed to be added (the WITH CHECK case).
If row-level security is enabled for a table but no applicable policies exist, a "default deny" policy is assumed, so no rows are visible or updatable.
Parameters
name
The name of the policy to be created. This must be distinct from the name of any other existing policy on the table.
table_name
The name of the table to which the policy applies (can be schema-qualified).
PERMISSIVE
Specifies that the policy is to be created as a permissive policy. All permissive policies applicable to a given query will be combined using the Boolean "OR" operator. By creating permissive policies, administrators can increase the set of records that can be accessed. Policies are permissive by default.
RESTRICTIVE
Specifies that the policy is to be created as a restrictive policy. All restrictive policies applicable to a given query will be combined using the Boolean "AND" operator. By creating restrictive policies, administrators can reduce the set of records that can be accessed, because each record must pass all restrictive policies.
Note that at least one permissive policy is required to grant access to records before restrictive policies can actually reduce access. If only restrictive policies exist, no records can be accessed. When permissive and restrictive policies coexist, a record is accessible only when it passes at least one permissive policy and all restrictive policies.
command
The command to which the policy applies. Valid options are ALL, SELECT, INSERT, UPDATE, and DELETE. ALL is the default. See below for details on how these policies are applied.
role_name
The role(s) to which the policy applies. The default is PUBLIC, which will apply the policy to all roles.
using_expression
An arbitrary SQL conditional expression (returning boolean). The conditional expression cannot contain any aggregate or window functions. If row-level security is enabled, this expression will be added to queries referencing the table. Rows for which this expression returns true will be visible. Rows for which this expression returns false or null will be invisible to the user (in SELECT) and unavailable for modification (in UPDATE or DELETE). Such rows will be silently suppressed without reporting an error.
check_expression
An arbitrary SQL conditional expression (returning boolean). The conditional expression cannot contain any aggregate or window functions. If row-level security is enabled, this expression will be used in INSERT and UPDATE queries on the table. Only rows for which the expression evaluates to true are allowed. If any inserted record or updated record causes the expression to evaluate to false or null, an error will be thrown. Note that check_expression is evaluated against the new contents of the row, not the original contents.
Per-Command Policies
ALL
Using ALL for a policy means that it applies to all commands regardless of command type. If an ALL policy exists along with more specific policies, both the ALL policy and those specific policies will be applied. Furthermore, the ALL policy will apply to both the selection side and the modification side of a query; if only a USING expression is defined, the USING expression will be used for both cases.
For example, if an UPDATE is issued, the ALL policy will simultaneously affect which rows the UPDATE can update (applying the USING expression) and whether the updated rows are allowed to be added to the table (if a WITH CHECK expression is defined, it is applied; otherwise, the USING expression is used). If an INSERT or UPDATE command attempts to add a row to the table that does not pass the ALL policy's WITH CHECK expression, the entire statement will be aborted.
SELECT
Using SELECT for a policy means it applies to SELECT queries, and it is required whenever SELECT privilege on the relation is needed. The result is that during a SELECT query, only records in the relation that pass the SELECT policy will be returned, and queries that require SELECT privilege, such as UPDATE, will also only be able to see rows allowed by the SELECT policy. A SELECT policy cannot have a WITH CHECK expression because it only applies to the case of retrieving records from the relation.
INSERT
Using INSERT for a policy means it applies to INSERT commands. Rows being inserted that do not pass this policy will result in a policy violation error, and the entire INSERT command will be aborted. An INSERT policy cannot have a USING expression because it only applies to the case of adding records to the relation.
Note that in an INSERT with ON CONFLICT DO UPDATE, only the WITH CHECK expression of the INSERT policy is checked for rows appended to the relation via the INSERT path.
UPDATE
Using UPDATE for a policy means it will apply to UPDATE, SELECT FOR UPDATE, and SELECT FOR SHARE commands, as well as the auxiliary ON CONFLICT DO UPDATE clause of INSERT commands. Since UPDATE requires extracting existing records and replacing them with newly modified records, the UPDATE policy accepts both USING and WITH CHECK expressions. The USING expression determines which records the UPDATE command can see to operate on, and the WITH CHECK expression defines which modified rows are allowed to be stored back into the relation.
Any row with updated values that does not pass the WITH CHECK expression will cause an error, and the entire command will be aborted. If only a USING clause is specified, then that clause will be used for both USING and WITH CHECK cases.
Typically, an UPDATE command also needs to read data from columns in the relation being updated (for example, in the WHERE clause, RETURNING clause, or in expressions on the right-hand side of the SET clause). In this case, SELECT privilege on the relation being updated is also required, and in addition to the UPDATE policy, the appropriate SELECT or ALL policy must also be applied. Thus, in addition to being authorized by the UPDATE or ALL policy to update rows, the user must also be able to access the rows being updated through the SELECT or ALL policy.
When an INSERT command has an ON CONFLICT DO UPDATE clause, if the UPDATE path is taken, the row to be updated is first checked against the USING expression of any UPDATE policy, and then the newly modified row is checked against the WITH CHECK expression. Note, however, that unlike a standalone UPDATE command, if an existing row fails the USING expression check, an error is thrown (the UPDATE path is never silently avoided).
DELETE
Using DELETE for a policy means it applies to DELETE commands. Only rows that pass this policy will be visible to the DELETE command. If some rows do not pass the DELETE policy's USING expression, they can still be seen via SELECT but cannot be deleted.
In most cases, a DELETE command also needs to read data from columns in the relation from which it is deleting (for example, in the WHERE clause or RETURNING clause). In this case, SELECT privilege on the relation is also required, and in addition to the DELETE policy, the appropriate SELECT or ALL policy must be applied. Thus, in addition to being authorized by the DELETE or ALL policy to delete rows, the user must also be able to access the rows being deleted through the SELECT or ALL policy.
A DELETE policy cannot have a WITH CHECK expression because it only applies to the case of deleting records from the relation, so there are no new rows to check.
Application of Multiple Policies
When multiple policies of different command types apply to the same command (for example, when SELECT and UPDATE policies both apply to an UPDATE command), the user must have both types of privileges (for example, the privilege to select rows from the relation and to update it). The expressions of one policy type are then combined with the expressions of the other policy type using the AND operator.
When multiple policies of the same command type apply to the same command, then at least one PERMISSIVE policy must authorize access to the relation, and all RESTRICTIVE policies must be passed. Thus, all PERMISSIVE policy expressions are combined with OR, all RESTRICTIVE policy expressions are combined with AND, and the results are combined with AND. If there are no PERMISSIVE policies, access is denied.
Note that for the purpose of combining multiple policies, ALL policies are treated as having the same type as any other policy being applied.
For example, in an UPDATE command, both SELECT and UPDATE privileges are required. If there are multiple applicable policies for each type, they are combined as follows:
expression from RESTRICTIVE SELECT/ALL policy 1
AND
expression from RESTRICTIVE SELECT/ALL policy 2
AND
...
AND
(
expression from PERMISSIVE SELECT/ALL policy 1
OR
expression from PERMISSIVE SELECT/ALL policy 2
OR
...
)
AND
expression from RESTRICTIVE UPDATE/ALL policy 1
AND
expression from RESTRICTIVE UPDATE/ALL policy 2
AND
...
AND
(
expression from PERMISSIVE UPDATE/ALL policy 1
OR
expression from PERMISSIVE UPDATE/ALL policy 2
OR
...
)
Notes
To create or modify policies for a table, you must be the owner of the table.
Although policies are applied to explicit queries against tables in the database, they are not applied when the system is performing internal referential integrity checks or validating constraints. This means there are indirect ways to determine whether a given value exists.
One example is attempting to insert a duplicate value into a column that is a primary key or has a unique constraint. If the insert fails, the user can infer that the value already exists (this example assumes the user is allowed by policy to insert records they cannot see). Another example is a user who is allowed to insert into a table that references another table, where the other table is hidden. By inserting a value into the referencing table, the user can determine the existence of the value; a successful insert indicates the value exists in the referenced table. To address these issues, policies should be carefully crafted to completely prevent users from inserting, deleting, or updating records that might indicate the existence of values they cannot see, or generated values (such as surrogate keys) should be used instead of keys with external meaning.
Typically, the system will enforce filtering conditions imposed by security policies before applying conditions present in the user's query, to prevent inadvertently exposing protected data to potentially untrusted user-defined functions. However, functions and operators marked as LEAKPROOF by the system (or the system administrator) can be evaluated before policy expressions, because they are assumed to be trusted.
Because policy expressions are added directly to user queries, they run with the privileges of the user executing the entire query.
Therefore, a user using a given policy must be able to access any tables or functions referenced in the expression; otherwise, they will simply receive a permission error when attempting to query a table with row-level security enabled. However, this does not change how views work. For ordinary queries and views, permission checks and policies on tables referenced by the view use the view owner's privileges and any policies applicable to the view owner.